-- length limit imposed due to limitations of Internet Explorer 8 is no longer needed
-- it drops the length limit: VARCHAR(32768) -> VARCHAR
SELECT fn_db_change_column_type('vm_icons', 'data_url', 'VARCHAR', 'VARCHAR');

-- make sure that icon of content 'icon_data' (datauri string) exists and return its id
CREATE OR REPLACE FUNCTION __temp_GetOrCreateIcon(icon_data VARCHAR)
    RETURNS UUID AS
$procedure$
DECLARE
    icon_id UUID := (SELECT id FROM vm_icons WHERE data_url = icon_data);
BEGIN
    IF icon_id IS NULL THEN
        INSERT INTO vm_icons(id, data_url)
        VALUES (uuid_in(md5(random()::text)::cstring), icon_data)
        RETURNING id INTO icon_id;
    END IF;
    RETURN icon_id;
END;
$procedure$
LANGUAGE plpgsql;

-- set new icon pairs (small + large) for VM and Templates that uses default icons as defined in vm_icon_defaults table
DO LANGUAGE plpgsql $block$
DECLARE
    small_debian_icon_data CONSTANT VARCHAR := '';
    small_free_bsd_icon_data CONSTANT VARCHAR := '';
    small_other_linux_icon_data CONSTANT VARCHAR := '';
    small_other_unknown_icon_data CONSTANT VARCHAR := '';
    small_rhel_atomic_icon_data CONSTANT VARCHAR := '';
    small_rhel_icon_data CONSTANT VARCHAR := '';
    small_suse_icon_data CONSTANT VARCHAR := '';
    small_ubuntu_icon_data CONSTANT VARCHAR := '';
    small_windows_icon_data CONSTANT VARCHAR := '';

    large_debian_icon_data CONSTANT VARCHAR := '';
    large_free_bsd_icon_data CONSTANT VARCHAR := '';
    large_other_linux_icon_data CONSTANT VARCHAR := '';
    large_other_unknown_icon_data CONSTANT VARCHAR := '';
    large_rhel_atomic_icon_data CONSTANT VARCHAR := '';
    large_rhel_icon_data CONSTANT VARCHAR := '';
    large_suse_icon_data CONSTANT VARCHAR := '';
    large_ubuntu_icon_data CONSTANT VARCHAR := '';
    large_windows_icon_data CONSTANT VARCHAR := '';

    small_debian_icon_id UUID := __temp_GetOrCreateIcon(small_debian_icon_data);
    small_free_bsd_icon_id UUID := __temp_GetOrCreateIcon(small_free_bsd_icon_data);
    small_other_linux_icon_id UUID := __temp_GetOrCreateIcon(small_other_linux_icon_data);
    small_other_unknown_icon_id UUID := __temp_GetOrCreateIcon(small_other_unknown_icon_data);
    small_rhel_atomic_icon_id UUID := __temp_GetOrCreateIcon(small_rhel_atomic_icon_data);
    small_rhel_icon_id UUID := __temp_GetOrCreateIcon(small_rhel_icon_data);
    small_suse_icon_id UUID := __temp_GetOrCreateIcon(small_suse_icon_data);
    small_ubuntu_icon_id UUID := __temp_GetOrCreateIcon(small_ubuntu_icon_data);
    small_windows_icon_id UUID := __temp_GetOrCreateIcon(small_windows_icon_data);

    large_debian_icon_id UUID := __temp_GetOrCreateIcon(large_debian_icon_data);
    large_free_bsd_icon_id UUID := __temp_GetOrCreateIcon(large_free_bsd_icon_data);
    large_other_linux_icon_id UUID := __temp_GetOrCreateIcon(large_other_linux_icon_data);
    large_other_unknown_icon_id UUID := __temp_GetOrCreateIcon(large_other_unknown_icon_data);
    large_rhel_atomic_icon_id UUID := __temp_GetOrCreateIcon(large_rhel_atomic_icon_data);
    large_rhel_icon_id UUID := __temp_GetOrCreateIcon(large_rhel_icon_data);
    large_suse_icon_id UUID := __temp_GetOrCreateIcon(large_suse_icon_data);
    large_ubuntu_icon_id UUID := __temp_GetOrCreateIcon(large_ubuntu_icon_data);
    large_windows_icon_id UUID := __temp_GetOrCreateIcon(large_windows_icon_data);
BEGIN
    -- IDs of guest OSes are stored in packaging/conf/osinfo-defaults.properties
    UPDATE vm_static
        SET small_icon_id =
            (CASE
                WHEN os IN (1300) THEN small_debian_icon_id
                WHEN os IN (1500) THEN small_free_bsd_icon_id
                WHEN os IN (5, 1002) THEN small_other_linux_icon_id
                WHEN os IN (0, 1001) THEN small_other_unknown_icon_id
                WHEN os IN (28) THEN small_rhel_atomic_icon_id
                WHEN os IN (7, 8, 9, 13, 14, 15, 18, 19, 1003, 1006, 1007) THEN small_rhel_icon_id
                WHEN os IN (1004, 1193) THEN small_suse_icon_id
                WHEN os IN (1005, 1252, 1253, 1254, 1255, 1256) THEN small_ubuntu_icon_id
                WHEN os IN (1, 3, 4, 10, 11, 12, 16, 17, 20, 21, 23, 25, 26, 27, 29) THEN small_windows_icon_id
                ELSE small_other_unknown_icon_id
                END),
            large_icon_id =
            (CASE
                 WHEN os IN (1300) THEN large_debian_icon_id
                 WHEN os IN (1500) THEN large_free_bsd_icon_id
                 WHEN os IN (5, 1002) THEN large_other_linux_icon_id
                 WHEN os IN (0, 1001) THEN large_other_unknown_icon_id
                 WHEN os IN (28) THEN large_rhel_atomic_icon_id
                 WHEN os IN (7, 8, 9, 13, 14, 15, 18, 19, 1003, 1006, 1007) THEN large_rhel_icon_id
                 WHEN os IN (1004, 1193) THEN large_suse_icon_id
                 WHEN os IN (1005, 1252, 1253, 1254, 1255, 1256) THEN large_ubuntu_icon_id
                 WHEN os IN (1, 3, 4, 10, 11, 12, 16, 17, 20, 21, 23, 25, 26, 27, 29) THEN large_windows_icon_id
                 ELSE large_other_unknown_icon_id
                 END)
        FROM vm_icon_defaults
        WHERE vm_static.entity_type IN ('VM', 'TEMPLATE')
            AND vm_static.small_icon_id = vm_icon_defaults.small_icon_id
            AND vm_static.large_icon_id = vm_icon_defaults.large_icon_id
            AND vm_static.os = vm_icon_defaults.os_id;
END $block$;

DROP FUNCTION IF EXISTS __temp_GetOrCreateIcon()
